// BorrowRecordDAO.java - 修复Date问题的完整版本
package com.library.dao;

import com.library.entity.BorrowRecord;
import com.library.util.DBUtil;
import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

public class BorrowRecordDAO {

    /**
     * 获取读者的借阅记录
     */
    public List<BorrowRecord> getReaderBorrowRecords(String readerId) {
        List<BorrowRecord> records = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT record_id, reader_id, book_isbn, borrow_date, due_date, " +
                    "return_date, renew_count, status, fine_amount " +
                    "FROM borrow_records WHERE reader_id = ? ORDER BY borrow_date DESC";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, readerId);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                BorrowRecord record = resultSetToBorrowRecord(rs);
                records.add(record);
            }
        } catch (SQLException e) {
            System.err.println("获取读者借阅记录失败: " + e.getMessage());
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }
        return records;
    }

    /**
     * 获取所有借阅记录
     */
    public List<BorrowRecord> getAllBorrowRecords() {
        List<BorrowRecord> records = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT record_id, reader_id, book_isbn, borrow_date, due_date, " +
                    "return_date, renew_count, status, fine_amount " +
                    "FROM borrow_records ORDER BY borrow_date DESC";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                BorrowRecord record = resultSetToBorrowRecord(rs);
                records.add(record);
            }
        } catch (SQLException e) {
            System.err.println("获取所有借阅记录失败: " + e.getMessage());
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }
        return records;
    }

    /**
     * 根据记录ID获取借阅记录
     */
    public BorrowRecord getBorrowRecordById(String recordId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT record_id, reader_id, book_isbn, borrow_date, due_date, " +
                    "return_date, renew_count, status, fine_amount " +
                    "FROM borrow_records WHERE record_id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, recordId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                return resultSetToBorrowRecord(rs);
            }
        } catch (SQLException e) {
            System.err.println("根据ID获取借阅记录失败: " + e.getMessage());
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }
        return null;
    }

    /**
     * 添加借阅记录
     */
    public boolean addBorrowRecord(BorrowRecord record) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "INSERT INTO borrow_records (record_id, reader_id, book_isbn, borrow_date, " +
                    "due_date, return_date, renew_count, status, fine_amount) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";

            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, record.getRecordId());
            pstmt.setString(2, record.getReaderId());
            pstmt.setString(3, record.getBookIsbn());
            pstmt.setDate(4, java.sql.Date.valueOf(record.getBorrowDate()));
            pstmt.setDate(5, java.sql.Date.valueOf(record.getDueDate()));

            // 处理可能为空的归还日期
            if (record.getReturnDate() != null) {
                pstmt.setDate(6, java.sql.Date.valueOf(record.getReturnDate()));
            } else {
                pstmt.setNull(6, Types.DATE);
            }

            pstmt.setInt(7, record.getRenewCount());
            pstmt.setString(8, record.getStatus().name());
            pstmt.setDouble(9, record.getFineAmount());

            int result = pstmt.executeUpdate();
            return result > 0;

        } catch (SQLException e) {
            System.err.println("添加借阅记录失败: " + e.getMessage());
            return false;
        } finally {
            DBUtil.closeAll(conn, pstmt, null);
        }
    }

    /**
     * 更新借阅记录
     */
    public boolean updateBorrowRecord(BorrowRecord record) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "UPDATE borrow_records SET reader_id=?, book_isbn=?, borrow_date=?, " +
                    "due_date=?, return_date=?, renew_count=?, status=?, fine_amount=? " +
                    "WHERE record_id=?";

            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, record.getReaderId());
            pstmt.setString(2, record.getBookIsbn());
            pstmt.setDate(3, java.sql.Date.valueOf(record.getBorrowDate()));
            pstmt.setDate(4, java.sql.Date.valueOf(record.getDueDate()));

            // 处理可能为空的归还日期
            if (record.getReturnDate() != null) {
                pstmt.setDate(5, java.sql.Date.valueOf(record.getReturnDate()));
            } else {
                pstmt.setNull(5, Types.DATE);
            }

            pstmt.setInt(6, record.getRenewCount());
            pstmt.setString(7, record.getStatus().name());
            pstmt.setDouble(8, record.getFineAmount());
            pstmt.setString(9, record.getRecordId());

            int result = pstmt.executeUpdate();
            return result > 0;

        } catch (SQLException e) {
            System.err.println("更新借阅记录失败: " + e.getMessage());
            return false;
        } finally {
            DBUtil.closeAll(conn, pstmt, null);
        }
    }

    /**
     * 删除借阅记录
     */
    public boolean deleteBorrowRecord(String recordId) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "DELETE FROM borrow_records WHERE record_id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, recordId);

            int result = pstmt.executeUpdate();
            return result > 0;

        } catch (SQLException e) {
            System.err.println("删除借阅记录失败: " + e.getMessage());
            return false;
        } finally {
            DBUtil.closeAll(conn, pstmt, null);
        }
    }

    /**
     * 续借图书
     */
    public boolean renewBook(String recordId, int additionalDays) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();

            // 获取当前借阅记录
            BorrowRecord record = getBorrowRecordById(recordId);
            if (record == null) {
                return false;
            }

            // 计算新的应还日期
            LocalDate newDueDate = record.getDueDate().plusDays(additionalDays);

            // 更新续借次数和应还日期
            String sql = "UPDATE borrow_records SET due_date = ?, renew_count = renew_count + 1 " +
                    "WHERE record_id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setDate(1, java.sql.Date.valueOf(newDueDate));
            pstmt.setString(2, recordId);

            int result = pstmt.executeUpdate();
            return result > 0;

        } catch (SQLException e) {
            System.err.println("续借图书失败: " + e.getMessage());
            return false;
        } finally {
            DBUtil.closeAll(conn, pstmt, null);
        }
    }

    /**
     * 归还图书
     */
    public boolean returnBook(String recordId) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "UPDATE borrow_records SET return_date = ?, status = 'RETURNED' " +
                    "WHERE record_id = ?";

            pstmt = conn.prepareStatement(sql);
            pstmt.setDate(1, java.sql.Date.valueOf(LocalDate.now()));
            pstmt.setString(2, recordId);

            int result = pstmt.executeUpdate();
            return result > 0;

        } catch (SQLException e) {
            System.err.println("归还图书失败: " + e.getMessage());
            return false;
        } finally {
            DBUtil.closeAll(conn, pstmt, null);
        }
    }

    /**
     * 获取超期借阅记录
     */
    public List<BorrowRecord> getOverdueRecords() {
        List<BorrowRecord> records = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT record_id, reader_id, book_isbn, borrow_date, due_date, " +
                    "return_date, renew_count, status, fine_amount " +
                    "FROM borrow_records WHERE due_date < ? AND status = 'BORROWED' " +
                    "ORDER BY due_date ASC";

            pstmt = conn.prepareStatement(sql);
            pstmt.setDate(1, java.sql.Date.valueOf(LocalDate.now()));
            rs = pstmt.executeQuery();

            while (rs.next()) {
                BorrowRecord record = resultSetToBorrowRecord(rs);
                records.add(record);
            }
        } catch (SQLException e) {
            System.err.println("获取超期借阅记录失败: " + e.getMessage());
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }
        return records;
    }

    /**
     * 获取读者的当前借阅数量
     */
    public int getCurrentBorrowCount(String readerId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT COUNT(*) as count FROM borrow_records " +
                    "WHERE reader_id = ? AND status = 'BORROWED'";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, readerId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getInt("count");
            }
        } catch (SQLException e) {
            System.err.println("获取读者当前借阅数量失败: " + e.getMessage());
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }
        return 0;
    }

    /**
     * 获取图书的借阅历史
     */
    public List<BorrowRecord> getBookBorrowHistory(String bookIsbn) {
        List<BorrowRecord> records = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT record_id, reader_id, book_isbn, borrow_date, due_date, " +
                    "return_date, renew_count, status, fine_amount " +
                    "FROM borrow_records WHERE book_isbn = ? ORDER BY borrow_date DESC";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, bookIsbn);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                BorrowRecord record = resultSetToBorrowRecord(rs);
                records.add(record);
            }
        } catch (SQLException e) {
            System.err.println("获取图书借阅历史失败: " + e.getMessage());
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }
        return records;
    }

    /**
     * 将ResultSet转换为BorrowRecord对象
     */
    private BorrowRecord resultSetToBorrowRecord(ResultSet rs) throws SQLException {
        BorrowRecord record = new BorrowRecord(
                rs.getString("record_id"),
                rs.getString("reader_id"),
                rs.getString("book_isbn"),
                // 使用 java.sql.Date 的 toLocalDate() 方法
                rs.getDate("borrow_date").toLocalDate(),
                rs.getDate("due_date").toLocalDate()
        );

        // 处理可能为空的归还日期
        java.sql.Date returnDate = rs.getDate("return_date");
        if (returnDate != null && !rs.wasNull()) {
            record.setReturnDate(returnDate.toLocalDate());
        }

        record.setRenewCount(rs.getInt("renew_count"));
        record.setFineAmount(rs.getDouble("fine_amount"));

        String status = rs.getString("status");
        if (status != null) {
            try {
                record.setStatus(BorrowRecord.BorrowStatus.valueOf(status));
            } catch (IllegalArgumentException e) {
                record.setStatus(BorrowRecord.BorrowStatus.BORROWED);
            }
        }

        return record;
    }
}